'''
文本转移
'''
import xlrd
import xlwt
import openpyxl
import sys
import xlsxwriter
from openpyxl.utils import get_column_letter
from openpyxl import Workbook
import inspect
from openpyxl.styles import Border, Side, colors
from openpyxl.styles import Alignment
from openpyxl.styles import Font
import os

#ws = Workbook('土地分类面积表2.xlsx')

list = []  # 存储数据表QSDWMC中去重后的名称
sarea = []  # 存储数据表Shape_Area数值
dlmc = []  # 存储数据表DLMC名称
qsdwmc = []  # 存储数据表QSDWMC中未去重的名称
# linenumber = [0, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29,
#              30]
# landtypes = {'水田': 2, '水浇地': 3, '旱地': 4, '果园': 5, '有林地': 6, '沟渠': 7,
#              '道路': 8, '坑塘水面': 9, '设施农用地': 10, '田坎': 11, '仓储用地': 13,
#              '科教用地': 14, '城市': 15, '建制镇': 16, '村庄': 17, '公路': 18, '水利设施用地': 19,
#              '墓葬地': 20, '其他草地': 21}
land = ['水田', '水浇地', '旱地', '果园', '有林地', '沟渠',
        '道路', '坑塘水面', '设施农用地', '田坎', '仓储用地',
        '科教用地', '城市', '建制镇', '村庄', '公路', '水利设施用地',
        '墓葬地', '其他草地']
types = [2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 13, 14, 15, 16, 17, 18, 19, 20, 21]  # 需要输入数据的序列

types2 = ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U']  # 需要输入数据的序列


# def get_variable_name(variable):
#     callers_local_vars = inspect.currentframe().f_back.f_locals.items()
#     return [var_name for var_name, var_val in callers_local_vars if var_val is variable]


# 读取数据表中的数据
def readexcel():
    #file = 'qqq11.xls'
    file = '大国工匠违法套图1.xls'
    # 创建工作空间
    workbook = xlrd.open_workbook(file)

    # 选取第0个表
    sheet1 = workbook.sheet_by_index(0)

    # 获取第4列数据
    dlmclist = (sheet1.col_values(4))
    # 把第一行标题弹出去
    dlmclist.pop(0)
    print(dlmclist)
    dlmc_all = {'其他林地':'有林地','公路用地':'公路'}
    dlmc_1 = [dlmc_all[i] if i in dlmc_all else i for i in dlmclist]
    print(dlmc_1)
    # 获取第13列数据
    sarealist = (sheet1.col_values((13)))
    sarealist.pop(0)

    n1 = len(sarealist)
    # 数据添加到列表
    for a in range(0, n1):
        sarea.append(sarealist[a])

    n2 = len(dlmc_1)
    for b in range(0, n2):
        dlmc.append(dlmc_1[b])

    # dlmclist.append(sheet1.col_values(4))
    # 获取第5列数据
    iplist = (sheet1.col_values(5))
    iplist.pop(0)  # 把第一行标题弹出去
    for i in iplist:
        if i not in list:  # 去掉重复的
            list.append(i)
    print(list)
    # print(sheet1.cell_value(row,5))
    # n3 = len(iplist)
    for b in range(0, n2):  # 将有未去重的数据添加到列表
        qsdwmc.append(iplist[b])

# 在模板中写入数据
def writeexcel():
    file = '土地分类面积表2.xlsx'

    workbook = openpyxl.load_workbook(file)  # 创建工作空间
    sheet1 = workbook['Sheet1']  # 选取Sheet1表


    # 边框设置
    border_set = Border(left=Side(style='medium', color=colors.BLACK),
                        right=Side(style='medium', color=colors.BLACK),
                        top=Side(style='medium', color=colors.BLACK),
                        bottom=Side(style='medium', color=colors.BLACK))
    # sheet1['B12'].border = border_set
    # workbook.save('test1.xlsx')
    font1 = Font(name=u'Calibri', bold=False, italic=False, size=10.5)
    # 写入第一列
    n = len(list)
    for i in range(0, n):
        workbook.active.insert_rows(4 + i)  # 在第四行前插入一行，无格式
        # sheet1.row_dimensions[4+i].border = border_set
        for j in range(1, 23):
            sheet1.cell(row=4 + i, column=j).border = border_set  # 设置插入行的单元格格式
            sheet1.cell(row=4 + i, column=j).font = font1
            sheet1.cell(row=4 + i, column=j).number_format = '0.00'  # 设置插入行的单元格数字格式为保留两位小数
            # 设置单元格自动换行，居中
            sheet1.cell(row = 4+i,column = j).alignment = Alignment(horizontal='center', vertical='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0)
            # sheet1.cell(row=4 + i, column=j).alignment = Alignment(wrapText=True)  # 设置单元格自动换行
        m = len(list[i])  # 通过数据长度修改行高
        if m > 4:
            sheet1.row_dimensions[4 + i].height = 30
        if m > 8:
            sheet1.row_dimensions[4 + i].height = 45
        sheet1.cell(4 + i, 1).value = list[i]  # 写入单元格
    print('保存test1')
    workbook.save('test1.xls')

    # 循环输入
    ldlen = len(land)
    llen = len(list)
    qlen = len(qsdwmc)

    qsdwmc.append('结束')
    for l1 in range(0, llen):  # 确定行数
        print('搜索')
        for ld1 in range(0, ldlen):  # 循环，换行
            print('查看')
            print(qsdwmc)
            print(list[l1])
            if list[l1] == qsdwmc[0]:  # 根据行标题写入行数据

                for ld1 in range(0, ldlen):  # 遍历land
                    print(land[ld1])
                    print(dlmc[0])
                    if land[ld1] == dlmc[0]:  # 判断匹配
                        print(1111)
                        print(dlmc[0])
                        # 其他写入方法
                        # sheet1['%s%s' % (types2[ld1],types3[l1])] = sarea[0]
                        # sheet1.cell(row = l1 + 4,column = types[ld1],value = sarea[0])

                        data = sheet1.cell(4 + l1, types[ld1]).value  # 用于判断单元格是否为空
                        sum1 = sheet1.cell(4 + l1, 22).value  # 用于同名称数据相加

                        if data is None:  # 判断是否为空
                            sheet1.cell(4 + l1, types[ld1]).value = sarea[0]  # 空值直接写入
                            workbook.save('test1.xlsx')

                            #sheet1.column_dimensions[types2[ld1]].width = 8  # 写入后调整列宽
                            workbook.save('test1.xlsx')
                        else:
                            sarea2 = data + sarea[0]  # 非空值进行相加后写入
                            sheet1.cell(4 + l1, types[ld1]).value = sarea2
                            workbook.save('test1.xlsx')

                        if sum1 is None:  # 判断合计列单元格是否为空
                            sheet1.cell(4 + l1, 22).value = sarea[0]  # 空值直接写入
                            workbook.save('test1.xlsx')
                        #     sheet1.column_dimensions['V'].width = 8   # 设置列宽
                        #     workbook.save('test1.xlsx')
                        else:
                            sum = sum1 + sarea[0]  # # 非空值进行相加后写入
                            sheet1.cell(4 + l1, 22).value = sum
                            workbook.save('test1.xlsx')
                    else:
                        pass

                qsdwmc.pop(0)  # 删除第0个，以保证每次循环取数正确
                dlmc.pop(0)
                sarea.pop(0)
            elif qsdwmc[0] == '':  # 有效循环结束时，碰到空字符时结束全部循环
                return 0
            else:  # 判断不一样，跳过

                break

    workbook.save('test1.xlsx')  # 保存


# 计算合计部分
def summation():
    file = 'test1.xlsx'
    print('行动')
    workbook = xlrd.open_workbook(file)  # 创建工作空间
    sheet1 = workbook.sheet_by_index(0)  # 选取第0个表

    workbook2 = openpyxl.load_workbook(file)  # 创建工作空间
    sheet2 = workbook2['Sheet1']  # 选取Sheet1表

    types.append(22)  # 在types中添加一列，以获取全部有效数值
    types2.append('V')

    cols_sum = locals()  # 设置列和动态变量
    cols_list = locals()  # 设置列值动态变量
    a = 0
    for i in types:
        cols_list['cols_list_' + str(i - 1)] = []  # 创建动态变量列表
        cols_list['cols_list_' + str(i - 1)] = (sheet1.col_values(i - 1))  # 获取i-1列的值
        cols_list['cols_list_' + str(i - 1)].pop(0)  # 删除第0个值，为了删除前三个标题
        cols_list['cols_list_' + str(i - 1)].pop(0)
        cols_list['cols_list_' + str(i - 1)].pop(0)

        print(cols_list['cols_list_' + str(i - 1)])
        print(len(cols_list['cols_list_' + str(i - 1)]))
        lc = len(cols_list['cols_list_' + str(i - 1)])  # 有效数值的行数

        for j in cols_list['cols_list_' + str(i - 1)]:  # 循环求和
            print(a)
            data = sheet2.cell(lc + 3, i).value  # 用于检测单元格是否为空

            if j == '':  # 空值跳过
                #sheet2.column_dimensions[types2[a]].width =10
                pass

            elif data is None:  # 单元格为空值加入数值
                sheet2.cell(lc + 3, i).value = j

            else:  # 求和
                sum2 = data + j
                sheet2.cell(lc + 3, i).value = sum2
                # sheet2.column_dimensions[i].width = len(sum2)  # 设置列宽
                # sheet2.column_dimensions[i].auto_size = True
                sumlen = len(str(sum2).split('.')[0])   # float无法计算len，先变成str再取整数部分
                sheet2.column_dimensions[types2[a]].width = sumlen+3.5  # 自适应列宽
                #print(len(str(sum2).split('.')[0]))


        a = a + 1
    sum_list = sheet2.cell(11, 22).value # 只是用于检测是否成功，可以删掉
    print(sum_list)
    workbook2.save('test2.xlsx')

    # a = get_variable_name(cols_list['cols_list_2']).pop()
    # print(a)

    # for a in range(0,len(cols1)):
    #     sarea.append(cols1[a])


if __name__ == '__main__':
    readexcel()
    writeexcel()
    summation()
